package cs.ui250.carnatic;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {
	private static final String DB_NAME = "Carnatic.db";
	private static final int DB_VERSION = 1;

	private static DBAdapter instance = null;

	private final Context context1;
	private DBHelper DBHelp;
	private SQLiteDatabase db;

	public static final String RAGA_TABLE = "ragas";

	public static final String RAGA_ID = "raga_id"; // index = 0
	public static final String RAGA_NAME = "raga_name";// index = 1
	public static final String RAGA_S = "S"; // index = 2
	public static final String RAGA_R1 = "R1"; // index = 3
	public static final String RAGA_R2 = "R2"; // index = 4
	public static final String RAGA_R3 = "R3"; // index = 5
	public static final String RAGA_G1 = "G1"; // index = 6
	public static final String RAGA_G2 = "G2"; // index = 7
	public static final String RAGA_G3 = "G3"; // index = 8
	public static final String RAGA_M1 = "M1"; // index = 9
	public static final String RAGA_M2 = "M2"; // index = 10
	public static final String RAGA_P = "P"; // index = 11
	public static final String RAGA_D1 = "D1"; // index = 12
	public static final String RAGA_D2 = "D2"; // index = 13
	public static final String RAGA_D3 = "D3"; // index = 14
	public static final String RAGA_N1 = "N1"; // index = 15
	public static final String RAGA_N2 = "N2"; // index = 16
	public static final String RAGA_N3 = "N3"; // index = 17

	public static final String RAGA_SONG = "raga_song"; // index = 18

	public static final String[] RAGA_COLS = { RAGA_ID, RAGA_NAME, RAGA_S,
			RAGA_R1, RAGA_R2, RAGA_R3, RAGA_G1, RAGA_G2, RAGA_G3, RAGA_M1,
			RAGA_M2, RAGA_P, RAGA_D1, RAGA_D2, RAGA_D3, RAGA_N1, RAGA_N2,
			RAGA_N3, RAGA_SONG };

	public static final String[] SWARA_COLS = { RAGA_S, RAGA_R1, RAGA_R2,
			RAGA_R3, RAGA_G1, RAGA_G2, RAGA_G3, RAGA_M1, RAGA_M2, RAGA_P,
			RAGA_D1, RAGA_D2, RAGA_D3, RAGA_N1, RAGA_N2, RAGA_N3 };

	// Singleton

	public static DBAdapter getInstance(Context context) {
		if (instance == null) {
			instance = new DBAdapter(context.getApplicationContext());
		}
		return instance;
	}

	private DBAdapter(Context context) {
		context1 = context;
		DBHelp = new DBHelper(context1, DB_NAME, null, DB_VERSION);

	}

	public void open() throws SQLiteException {
		try {
			db = DBHelp.getWritableDatabase();
		} catch (SQLiteException ex) {
			db = DBHelp.getReadableDatabase();
		}
	}

	public void close() {
		db.close();
	}

	public void fillRaga(Raga r) {
		ContentValues cvalues = new ContentValues();
		cvalues.put(RAGA_ID, r.getID());
		cvalues.put(RAGA_NAME, r.getName());
		cvalues.put(RAGA_S, r.getS());
		cvalues.put(RAGA_R1, r.getR1());
		cvalues.put(RAGA_R2, r.getR2());
		cvalues.put(RAGA_R3, r.getR3());
		cvalues.put(RAGA_G1, r.getG1());
		cvalues.put(RAGA_G2, r.getG2());
		cvalues.put(RAGA_G3, r.getG3());
		cvalues.put(RAGA_M1, r.getM1());
		cvalues.put(RAGA_M2, r.getM2());
		cvalues.put(RAGA_P, r.getP());
		cvalues.put(RAGA_D1, r.getD1());
		cvalues.put(RAGA_D2, r.getD2());
		cvalues.put(RAGA_D3, r.getD3());
		cvalues.put(RAGA_N1, r.getN1());
		cvalues.put(RAGA_N2, r.getN2());
		cvalues.put(RAGA_N3, r.getN3());
		cvalues.put(RAGA_SONG, r.getSongString());
		db.insert(RAGA_TABLE, null, cvalues);
	}

	public Raga getRaga(int id) {
		Cursor cursor = db.query(true, RAGA_TABLE, RAGA_COLS, RAGA_ID + "="
				+ id, null, null, null, null, null);
		if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			throw new SQLException("No raga found for row: " + id);
		}
		Raga r = new Raga(cursor.getInt(0), cursor.getString(1),
				cursor.getInt(2), cursor.getInt(3), cursor.getInt(4),
				cursor.getInt(5), cursor.getInt(6), cursor.getInt(7),
				cursor.getInt(8), cursor.getInt(9), cursor.getInt(10),
				cursor.getInt(11), cursor.getInt(12), cursor.getInt(13),
				cursor.getInt(14), cursor.getInt(15), cursor.getInt(16),
				cursor.getInt(17), cursor.getString(18));
		cursor.close();
		return r;
	}

	public Cursor ragaByName(String s) {

		Cursor C = db.query(RAGA_TABLE, new String[] { RAGA_ID, RAGA_NAME },
				RAGA_NAME + " LIKE '" + s + "%'", null, null, null, null);
		return C;
	}

	public Cursor ragaBySwara(int[] iarray) {
		String condition = "";
		for (int i = 0; i < 16; i++) {
			if (iarray[i] == 1) {
				condition += SWARA_COLS[i] + "=1 AND ";
			}
		}
		condition = condition.substring(0, condition.length() - 5);
		Cursor C = db.query(RAGA_TABLE, new String[] { RAGA_ID, RAGA_NAME },
				condition, null, null, null, null);
		return C;

	}

	private static class DBHelper extends SQLiteOpenHelper {

		private static final String DB_CREATE = "CREATE TABLE " + RAGA_TABLE
				+ " (" + RAGA_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ RAGA_NAME + " TEXT, " + RAGA_S + " INTEGER, " + RAGA_R1
				+ " INTEGER, " + RAGA_R2 + " INTEGER, " + RAGA_R3
				+ " INTEGER, " + RAGA_G1 + " INTEGER, " + RAGA_G2
				+ " INTEGER, " + RAGA_G3 + " INTEGER, " + RAGA_M1
				+ " INTEGER, " + RAGA_M2 + " INTEGER, " + RAGA_P + " INTEGER, "
				+ RAGA_D1 + " INTEGER, " + RAGA_D2 + " INTEGER, " + RAGA_D3
				+ " INTEGER, " + RAGA_N1 + " INTEGER, " + RAGA_N2
				+ " INTEGER, " + RAGA_N3 + " INTEGER, " + RAGA_SONG + " TEXT);";

		public DBHelper(Context context, String name, CursorFactory fct,
				int version) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DB_CREATE);
		}

		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS" + RAGA_TABLE);
			onCreate(db);
		}
	}
}